# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2Json


class CategoryDeptDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def insertCatDept(self, info):
        """
        添加category_dept信息
        :param info:
        :return:
        """
        categoryId = info['categoryId']
        deptIds = info['deptIds']

        cur = self.conn.cursor()
        # 根据category_id删除原有记录
        sql = "delete from zzh_category_dept WHERE category_id=%s"
        rs = cur.execute(sql, [categoryId])
        self.conn.commit()
        # 把新的记录插入进去
        for idd in deptIds:
            sql1 = "insert into zzh_category_dept (category_id, dept_id) VALUES " \
                  "(%s, %s)"
            rs = cur.execute(sql1, [categoryId, idd])
        self.conn.commit()
        # 取出新增的记录
        sql2 = "select * from zzh_category_dept WHERE category_id=%s"
        rs = cur.execute(sql2, [categoryId])
        rsJson = None
        if rs:
            rsData = cur.fetchall()
            rsJson = Table2Json.category_dept_list_to_json(rsData)
        cur.close()
        self.conn.close()
        return rsJson

    def findCatDeptById(self, id):
        """
        根据ID查询category_dept信息
        :param CatId:
        :return:
        """
        sql = "select * from zzh_category_dept WHERE id=%s"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [id])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.category_dept_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def findCatDeptByConditon(self, **kwargs):
        """
        category_dept表的条件查询
        :param kwargs:
        :return:
        """
        deptId = kwargs['deptId']

        sql = "select * from zzh_category_dept WHERE dept_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [deptId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.category_dept_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def deleteCatDept(self, idArray):
        """
        根据ID删除信息，可以传递一个或者多个ID，ID以数组形式传递
        :param idArray:
        :return:
        """
        sql = "delete from zzh_category_dept WHERE id=%s"
        cur = self.conn.cursor()
        for idd in idArray:
            rs = cur.execute(sql, [idd])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def updateCatDept(self, info):
        """
        更新category_dept
        :param info:
        :return:
        """
        catId = info['catId']
        categoryId = info['categoryId']
        deptId = info['deptId']

        sql = "update zzh_category_dept set category_id=%s, dept_id=%s WHERE id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [categoryId, deptId, catId])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def selectCatDept(self, catId):
        """
        根据ID查询，返回元组
        :param catId:
        :return: data tuple
        """
        sql = "select * from zzh_category_dept WHERE id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [catId])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr

    def findCatDeptByCatId(self, **kwargs):
        """
        根据catId 查询信息
        :param kwargs:
        :return:
        """
        deptId = kwargs['catId']

        sql = "select * from zzh_category_dept WHERE category_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [deptId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.category_dept_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons
